package com.zr.daoImpl;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.dao.UserDao;
import com.zr.model.AttendanceTable;
import com.zr.model.User;
import com.zr.util.JDBCUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;


public class UserDaoImpl implements UserDao{

	@Override
	public User getUserByNumAndPassword(String userNum, String userPsw) {
		// TODO Auto-generated method stub
		User user = new User();
		StringBuffer sql = new StringBuffer();
		sql.append(" select eid,ename,epsw");
		sql.append(" from employee");
		sql.append(" where eid=? and epsw=?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, userNum);
			pst.setString(2, userPsw);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				user.setUsernum(rs.getInt("eid"));
				user.setUsername(rs.getString("ename"));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user;
	}
	
	@Override
	public int getEntryCount() {
		int count = 0;
		StringBuffer  sql  = new StringBuffer("");
		sql.append(" select count(id) as number");
		sql.append(" from entry");
		Connection  con = JDBCUtil.getConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql.toString());
			ResultSet  rs = pst.executeQuery();
			if(rs.next()){
				count = rs.getInt("number");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}
	
	@Override
	public int getEntryCount(int did) {
		int count = 0;
		StringBuffer  sql  = new StringBuffer("");
		sql.append(" select count(id) as number");
		sql.append(" from entry");
		sql.append(" inner join employee");
		sql.append(" on entry.eid=employee.eid");
		sql.append(" where did=?");
		Connection  con = JDBCUtil.getConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql.toString());
			pst.setInt(1, did);
			ResultSet  rs = pst.executeQuery();
			if(rs.next()){
				count = rs.getInt("number");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}
	
	@Override
	public List<AttendanceTable> getEntryMSG(int page, int rows) {
		// TODO Auto-generated method stub
		List<AttendanceTable> Atts = new ArrayList<AttendanceTable>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select employee.eid,ename,dname,twork,owork");
		sql.append(" from employee");
		sql.append(" INNER JOIN department");
		sql.append(" ON employee.did = department.did");
		sql.append(" INNER JOIN entry");
		sql.append(" on entry.eid=employee.eid");
		sql.append(" limit ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, (page-1)*rows);
			pst.setInt(2, rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				AttendanceTable as = new AttendanceTable();
				as.setId(rs.getInt("employee.eid"));
				as.setUserName(rs.getString("ename"));
				as.setUserDpartment(rs.getString("dname"));
				as.setStartTime(rs.getString("twork"));
				as.setEndTime(rs.getString("owork"));
				Atts.add(as);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return Atts;
	}

	@Override
	public List<AttendanceTable> getEntryMSG(int did, int page, int rows) {
		List<AttendanceTable> Atts = new ArrayList<AttendanceTable>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select employee.eid,ename,dname,twork,owork");
		sql.append(" from employee");
		sql.append(" INNER JOIN department");
		sql.append(" ON employee.did = department.did");
		sql.append(" INNER JOIN entry");
		sql.append(" on entry.eid=employee.eid");
		sql.append(" where employee.did = ?");
		sql.append(" limit ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, did);
			pst.setInt(2, (page-1)*rows);
			pst.setInt(3, rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				AttendanceTable as = new AttendanceTable();
				as.setId(rs.getInt("employee.eid"));
				as.setUserName(rs.getString("ename"));
				as.setUserDpartment(rs.getString("dname"));
				as.setStartTime(rs.getString("twork"));
				as.setEndTime(rs.getString("owork"));
				Atts.add(as);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return Atts;
	}

	@Override
	public JSONArray getAllDepartment() {
		JSONArray   department =  new JSONArray();
		Connection  con   =  JDBCUtil.getConnection();
		StringBuffer  sql = new StringBuffer("");
		sql.append(" select did,dname");
		sql.append(" from department");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			ResultSet  rs  =  pst.executeQuery();
			while(rs.next()){
				  JSONObject  depart =  new JSONObject();
				  depart.put("id", rs.getInt("did"));
				  depart.put("text", rs.getString("dname"));
				  department.add(depart);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return department;
	}

	
	@Override
	public int getUserCount(int did) {
		int count = 0;
		StringBuffer  sql  = new StringBuffer("");
		sql.append(" select count(eid) as number");
		sql.append(" from employee");
		sql.append(" where did=?");
		Connection  con = JDBCUtil.getConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql.toString());
			pst.setInt(1, did);
			ResultSet  rs = pst.executeQuery();
			if(rs.next()){
				count = rs.getInt("number");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public List<User> getUserMSG(int did, int page, int rows) {
		List<User> Users = new ArrayList<User>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select eid,dname,eposition,ename,esex,eidcard,eage,eborn,enation,eaddress,eeducation,eintime,ephone,eremark");
		sql.append(" from employee");
		sql.append(" INNER JOIN department");
		sql.append(" ON employee.did = department.did");
		sql.append(" where employee.did = ?");
		sql.append(" limit ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, did);
			pst.setInt(2, (page-1)*rows);
			pst.setInt(3, rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				User use = new User();
				use.setUsernum(rs.getInt("eid"));
				use.setUsername(rs.getString("ename"));
				use.setUserdepartment(rs.getString("dname"));
				use.setUserpositon(rs.getString("eposition"));
				use.setUsersex(rs.getString("esex"));
				use.setUseridcard(rs.getString("eidcard"));
				use.setUserage(rs.getInt("eage"));
				use.setUserborn(rs.getString("eborn"));
				use.setUsernation(rs.getString("enation"));
				use.setUseraddress(rs.getString("eaddress"));
				use.setUsereducation(rs.getString("eeducation"));
				use.setUserintime(rs.getString("eintime"));
				use.setUserphone(rs.getString("ephone"));
				use.setUserremark(rs.getString("eremark"));
				Users.add(use);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return Users;
	}

}
